﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Framework;

namespace Enterprise
{
    public class TechnicalService : BaseService
    {
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select * from Technical where coTechnicalid={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(int pageValue = 0, int rowsValue = 0, string sort = "CoSort", string order = "Desc", string title = "", string language = "")
        {
            var parameters = new[]
                {
                    new OleDbParameter("@CoTitle", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10)

                };
            parameters[0].Value = title.SqlLike();
            parameters[1].Value = language.SqlLike();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where CoTitle like '%'+@CoTitle+'%' and CoLanguage like '%'+@CoLanguage+'%' ");
            int total = DbHelperOleDb.GetSingle("select count(*) from TbTechnical {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbTechnical {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(
                    " and CoTechnicalId not in (select top {0} CoTechnicalId from TbTechnical {1} order by {2} {3},cotechnicalid desc)  ",
                    rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by {0} {1},cotechnicalid desc ", sort, order);
            DataSet ds = DbHelperOleDb.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }

        public int Add(string title = "", string summary = "", string content = "", string keyword = "", string description = "", string author = "", string language = "zh_cn", DateTime? date = null, int sort = 0, bool isRecommend = false, bool isPopular = false, bool isTop = false, bool isDelete = false)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("insert into  TbTechnical(");
            sbSql.Append("CoTitle,CoSummary,CoContent,CoKeyword,CoDescription,CoLanguage,CoDate,CoAuthor,CoIsRecommend,CoIsPopular,CoIsTop,CoIsDelete,CoViews");
            sbSql.Append(") values (");
            sbSql.Append("@CoTitle,@CoSummary,@CoContent,@CoKeyword,@CoDescription,@CoLanguage,@CoDate,@CoAuthor,@CoIsRecommend,@CoIsPopular,@CoIsTop,@CoIsDelete,@CoViews");
            sbSql.Append(")");
            var parameters = new[]
                {
                    new OleDbParameter("@CoTitle", OleDbType.VarChar,255),
                    new OleDbParameter("@CoSummary", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar,255),
                    new OleDbParameter("@CoDescription", OleDbType.VarChar,255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoAuthor", OleDbType.VarChar,255),
                    new OleDbParameter("@CoIsRecommend", OleDbType.Boolean),
                    new OleDbParameter("@CoIsPopular", OleDbType.Boolean),
                    new OleDbParameter("@CoIsTop", OleDbType.Boolean),
                    new OleDbParameter("@CoIsDelete", OleDbType.Boolean),
                    new OleDbParameter("@CoViews", OleDbType.Integer)
      
                };
            parameters[0].Value = title;
            parameters[1].Value = summary;
            parameters[2].Value = content;
            parameters[3].Value = keyword;
            parameters[4].Value = description;
            parameters[5].Value = language;
            parameters[6].Value = date ?? DateTime.Now;
            parameters[7].Value = author;
            parameters[8].Value = isPopular;
            parameters[9].Value = isRecommend;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            parameters[12].Value = isDelete;
            parameters[13].Value = 0;
            var newId = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return newId;
        }
        public int Edit(int id = 0, string title = "", string summary = "", string content = "", string keyword = "", string description = "", string author = "", string language = "zh_cn", DateTime? date = null, int sort = 0, bool isRecommend = false, bool isPopular = false, bool isTop = false, bool isDelete = false)
        {
            var sbSql = new StringBuilder();
            sbSql.Append(" update TbTechnical set ");
            sbSql.Append(" CoTitle=@CoTitle,CoSummary=@CoSummary,CoContent=@CoContent,CoKeyword=@CoKeyword,CoDescription=@CoDescription,CoLanguage=@CoLanguage,CoAuthor=@CoAuthor,CoIsRecommend=@CoIsRecommend,CoIsPopular=@CoIsPopular,CoIsTop=@CoIsTop");
            sbSql.AppendFormat(" from where CoTechnicalId={0}", id);
            var parameters = new[]
                {
                    new OleDbParameter("@CoTitle", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSummary", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoAuthor", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoIsRecommend", OleDbType.Boolean),
                    new OleDbParameter("@CoIsPopular", OleDbType.Boolean),
                    new OleDbParameter("@CoIsTop", OleDbType.Boolean)
                };
            parameters[0].Value = title;
            parameters[1].Value = summary;
            parameters[2].Value = content;
            parameters[3].Value = keyword;
            parameters[4].Value = description;
            parameters[5].Value = language;
            parameters[6].Value = date ?? DateTime.Now;
            parameters[7].Value = author;
            parameters[8].Value = isPopular;
            parameters[9].Value = isRecommend;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            var count = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return count;
        }

        public int Delete(int id)
        {
            return DbHelperOleDb.ExecuteSql("delete from TbTechnical where CoTechnicalId={0}".FormatWith(id)).SafeToInt();
        }

    }
}
